package org.groupg;

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel 根据标题行、输入文件、输出目录、切割每页数量以及文件名切割文件
 */
public class ExcelSplitter {
    public static void main(String[] args) throws IOException {
        String inputFilePath = "D:\\DesktopFile\\05-文件\\押品价值内部重估信息批量导入模版.xlsx"; // 输入的Excel文件路径
        int rowsPerFile = 198; // 每份文件的行数
        String outputFolderPath = "D:\\DesktopFile\\05-文件\\Java\\"; // 输出文件夹路径
        String outFileName = "押品拆分导入文件";
        int titleNum = 2;
        splitExcelByRows(inputFilePath, outputFolderPath, outFileName, rowsPerFile, titleNum);
    }

    /**
     * 根据规则切分Excel
     *
     * @param inputFilePath    需要读取的Excel
     * @param outputFolderPath 输出目录
     * @param newFileName      新文件的名称，拼接格式为 文件名+i
     * @param rowsPerFile      每个Excel保留多少行
     * @param titleNum         标题行有多少航
     */
    public static void splitExcelByRows(String inputFilePath, String outputFolderPath, String newFileName, int rowsPerFile, int titleNum) throws IOException {
        File output = new File(outputFolderPath);
        if (output.isDirectory() && output.exists()) {
            FileUtils.deleteDirectory(output);
        }

        output.mkdirs();

        try (FileInputStream inputStream = new FileInputStream(new File(inputFilePath));
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
            List<Row> rowTitleList = new ArrayList<>(titleNum);
//            sheet.getRow();
            for (int i = 0; i < titleNum; i++) {
                rowTitleList.add(sheet.getRow(i));
            }
            int totalRows = sheet.getLastRowNum() - titleNum; // 获取去掉标题的总行数
            int numFiles = ((totalRows) / rowsPerFile) + 1; // 计算拆分后的文件数 去除标题行

            for (int i = 0; i < numFiles; i++) {
                int startRow = i * rowsPerFile + titleNum;
                int endRow;
                if (i < numFiles - 1) {
                    // 如果不是最后一行则结束位置为 i + 单页行数+标题行
                    endRow = (i + 1) * rowsPerFile + titleNum;
                } else {
                    endRow = totalRows + titleNum + 1;
                }


                Workbook newWorkbook = new XSSFWorkbook();
                Sheet subSheet = newWorkbook.createSheet(); // 创建新的工作表
                for (int j = 0; j < rowTitleList.size(); j++) {
                    copyRow(rowTitleList.get(j), subSheet.createRow(j));
                }
                for (int rowIndex = startRow; rowIndex < endRow; rowIndex++) {
                    Row row = sheet.getRow(rowIndex); // 获取指定行的数据
                    Row newRow = subSheet.createRow(rowIndex - startRow + titleNum); // 在新工作表中创建行
                    copyRow(row, newRow);
                }
                String newExcelFilePath = outputFolderPath + File.separator + newFileName + i + ".xlsx";
                FileOutputStream outputStream = new FileOutputStream(newExcelFilePath);
                newWorkbook.write(outputStream); // 将拆分后的工作簿写入输出流
                System.out.println("File " + newExcelFilePath); // 输出保存信息

                newWorkbook.close();
                outputStream.flush();
                outputStream.close();

            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 把 source 赋值给 target
     *
     * @param source 要复制成的行
     * @param target 复制到的目标
     */
    private static void copyRow(final Row source, final Row target) {
//        target.setRowStyle(source.getRowStyle());
        for (int i = source.getFirstCellNum(); i < source.getLastCellNum(); i++) {
            if (source.getCell(i) == null) continue;
            Cell targetCell = target.createCell(i, source.getCell(i).getCellType());
            switch (source.getCell(i).getCellType()) {
                case NUMERIC -> {
                    targetCell.setCellValue(source.getCell(i).getNumericCellValue());
                }
                case STRING -> {
                    targetCell.setCellValue(source.getCell(i).getStringCellValue());
                }
                case FORMULA -> {
                    targetCell.setCellValue(source.getCell(i).getCellFormula());
                }
                case BLANK -> {
                    targetCell.setCellValue("");
                }
                case BOOLEAN -> {
                    targetCell.setCellValue(source.getCell(i).getBooleanCellValue());
                }
                case ERROR -> {
                    targetCell.setCellValue(source.getCell(i).getErrorCellValue());
                }
                default -> throw new IllegalArgumentException("Unexpected value: " + source.getCell(i).getCellType());
            }
        }
    }
}